﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using ARS.Control.Attributes;
using ARS.Control.Custom.DataPaging.Attributes;
using DbSelect = ARS.Control.Attributes.DbSelect;


namespace ARS.DataBase.SqlServerProvider
{
	public class DataPagingProvider:ARS.DataBase.Provider.IDataPagingProvider
	{

		///// <summary>
		///// 获取分页数据
		///// </summary>
		///// <param name="selectModel"></param>
		///// <returns></returns>
		//public DataSet GetDataList(DbSelect selectModel)
		//{
		//    SqlParameter[] parameters =
		//    {
		//        new SqlParameter("@strTbName", SqlDbType.VarChar, 4000), //@tablename varchar(100),--表名或视图表 
		//        new SqlParameter("@strFeilds", SqlDbType.VarChar, 4000), //@fieldlist varchar(4000)='*',--欲选择字段列表 
		//        new SqlParameter("@OrderField", SqlDbType.VarChar, 100), //@orderfield varchar(100),--排序字段 
		//        new SqlParameter("@masterFeilds", SqlDbType.VarChar, 100), //@keyfield varchar(100),--主键 
		//        new SqlParameter("@PageIndex", SqlDbType.Int), //@pageindex int,--页号,从0开始 
		//        new SqlParameter("@PageSize", SqlDbType.Int), //@pagesize int=20,--页尺寸
		//        new SqlParameter("@strWhere", SqlDbType.VarChar, 4000), //@strwhere varchar(4000),--条件 
		//        new SqlParameter("@OrderType", SqlDbType.Bit), //@ordertype bit=1--排序,1,降序,0,升序
		//        new SqlParameter("@RecordCount", SqlDbType.Int), //@pagesize int=20,--页尺寸
		//        new SqlParameter("@totalPage", SqlDbType.Int), //@pagesize int=20,--页尺寸
		//    };
		//    parameters[0].Value = selectModel.TableName;
		//    parameters[1].Value = selectModel.SelectFields;
		//    parameters[2].Value = selectModel.OrderField;
		//    parameters[3].Value = selectModel.KeyField;
		//    parameters[4].Value = selectModel.PageIndex;
		//    parameters[5].Value = selectModel.PageSize;
		//    parameters[6].Value = selectModel.StrWhere;
		//    parameters[7].Value = selectModel.OrderType;
		//    parameters[8].Direction = ParameterDirection.Output;
		//    parameters[9].Direction = ParameterDirection.Output;
		//    // int RecordCount = 0;
		//    // int totalPage = 0;
		//    //using (SqlConnection connection = new SqlConnection(DbHelperSQL.connectionString))
		//    //{
		//    //    DataSet dataSet = new DataSet();
		//    //    connection.Open();
		//    //    SqlDataAdapter sqlDA = new SqlDataAdapter();
		//    //    sqlDA.SelectCommand = DbHelperSQL.BuildQueryCommand(connection, "GetPagePart", parameters);
		//    //    sqlDA.Fill(dataSet, "ds");
		//    //    connection.Close();
		//    //    return dataSet;
		//    //}
		//    return DbHelperSQL.RunProcedure("ARS_DataPaging", parameters, "ds");
		//}
        public override DataSet GetDataList(DbSelectAll dsa)
	    {
            return DbHelperSQL.Query(dsa.GetSelectAllSql());
	    }



	    /// <summary>
		/// 获取分页数据和结果数目
		/// </summary>
		/// <param name="selectModel"></param>
		/// <returns></returns>
        public override DataSet GetDataList(Control.Custom.DataPaging.Attributes.DbSelect selectModel, bool isStatistics = true)
		{
			SqlParameter[] parameters =
			{
				new SqlParameter("@TableName", SqlDbType.VarChar, 4000), //@tablename varchar(100),--表名或视图表 
				new SqlParameter("@SelectFields", SqlDbType.VarChar, 4000), //@fieldlist varchar(4000)='*',--欲选择字段列表 
				new SqlParameter("@OrderField", SqlDbType.VarChar, 100), //@orderfield varchar(100),--排序字段 
				new SqlParameter("@KeyField", SqlDbType.VarChar, 100), //@keyfield varchar(100),--主键 
				new SqlParameter("@PageIndex", SqlDbType.Int), //@pageindex int,--页号,从0开始 
				new SqlParameter("@PageSize", SqlDbType.Int), //@pagesize int=20,--页尺寸
				new SqlParameter("@StrWhere", SqlDbType.VarChar, 4000), //@strwhere varchar(4000),--条件 
				new SqlParameter("@OrderType", SqlDbType.Bit), //@ordertype bit=1--排序,1,降序,0,升序
				new SqlParameter("@ResultCount", SqlDbType.Int), //@pagesize int=20,--页尺寸
				new SqlParameter("@ResultPages", SqlDbType.Int), //@pagesize int=20,--页尺寸
			};
			parameters[0].Value = selectModel.TableName;
			parameters[1].Value = selectModel.SelectFields;
			parameters[2].Value = selectModel.OrderField;
			parameters[3].Value = selectModel.KeyField;
			parameters[4].Value = selectModel.PageIndex;
			parameters[5].Value = selectModel.PageSize;
			parameters[6].Value = selectModel.StrWhere;
			parameters[7].Value = selectModel.OrderType;
			parameters[8].Direction = ParameterDirection.Output;
			parameters[9].Direction = ParameterDirection.Output;
			if (isStatistics)
			{
				int RecordCount = 0;
				int totalPage = 0;
				using (SqlConnection connection = new SqlConnection(DbHelperSQL.connectionString))
				{
					DataSet dataSet = new DataSet();
					connection.Open();
					SqlDataAdapter sqlDA = new SqlDataAdapter();
					sqlDA.SelectCommand = DbHelperSQL.BuildQueryCommand(connection, base.GetDataPaging_List_ProcName(), parameters);
					sqlDA.Fill(dataSet, "ds");

					selectModel.ResultCount = Convert.ToInt32(sqlDA.SelectCommand.Parameters[8].Value);
					selectModel.ResultPages = Convert.ToInt32(sqlDA.SelectCommand.Parameters[9].Value);

					connection.Close();
					return dataSet;
				}
			}
			else
			{
				return DbHelperSQL.RunProcedure(base.GetDataPaging_List_ProcName(), parameters, "ds");

			}

		}


        public override DataPagingDb GetDataPagingDbModel(int id)
        {
            return this.GetDataPagingDbModel("Id", id.ToString());
		}

        public override DataPagingDb GetDataPagingDbModel(string name, string value)
		{
			StringBuilder strSql = new StringBuilder();
			//strSql.Append(base);
			strSql.Append(string.Format(base.GetSelectSql(), name));
			SqlParameter[] parameters = {
					new SqlParameter("@"+name, SqlDbType.Int,4)
			};
			parameters[0].Value = value;
			DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return base.GetDataPagingDbModel(ds);
		}

        public override DataPagingDb GetDataPagingDbModel(DataPagingQueryModel queryModel)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append(base);
            //strSql.Append(string.Format(base.GetSelectSql(), name));
            string strSql = string.Format("select * from {0} where Status_UserId=@Status_UserId and Status_ClassName=@Status_ClassName", GetDbTableName());
            SqlParameter[] parameters = {
					new SqlParameter("@Status_UserId", SqlDbType.Int,4),
                    new SqlParameter("@Status_ClassName", SqlDbType.VarChar,50)
			};
            parameters[0].Value = queryModel.UserId;
            parameters[1].Value = queryModel.ClassName;
            DataSet ds = DbHelperSQL.Query(strSql, parameters);
            return base.GetDataPagingDbModel(ds);
        }
        public override int DataPagingDbAdd(DataPagingDb model)
		{
            int rowsAffected;
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4),
					new SqlParameter("@Status_ClassName", SqlDbType.VarChar,100),
					new SqlParameter("@DbSelect_TableName", SqlDbType.VarChar,500),
					new SqlParameter("@DbSelect_SelectFields", SqlDbType.VarChar,500),
					new SqlParameter("@DbSelect_OrderField", SqlDbType.VarChar,100),
					new SqlParameter("@DbSelect_PageIndex", SqlDbType.Int,4),
					new SqlParameter("@DbSelect_PageSize", SqlDbType.Int,4),
					new SqlParameter("@DbSelect_StrWhere", SqlDbType.VarChar,500),
					new SqlParameter("@DbSelect_KeyField", SqlDbType.VarChar,50),
					new SqlParameter("@HtmlTable_TableCssName", SqlDbType.VarChar,50),
					new SqlParameter("@HtmlTable_TBodyTrHtml", SqlDbType.VarChar,4000),
					new SqlParameter("@Operat_OperatHtml", SqlDbType.VarChar,500),
					new SqlParameter("@Operat_IsHasOperat", SqlDbType.Bit,1),
					new SqlParameter("@Operat_ColumnName", SqlDbType.VarChar,500),
					new SqlParameter("@DbOperat_DeleteSql", SqlDbType.VarChar,500),
					new SqlParameter("@Status_UserId", SqlDbType.Int,4),
					new SqlParameter("@HtmlFanye_FanyeHtml", SqlDbType.VarChar,4000),
					new SqlParameter("@HtmlFanye_IsHasFanye", SqlDbType.Bit,1),
					new SqlParameter("@DbSelect_OrderType", SqlDbType.TinyInt,1),
					new SqlParameter("@HtmlTable_THeadTrHtml", SqlDbType.VarChar,500),
					new SqlParameter("@DbOperat_OperatSqlList", SqlDbType.VarChar,500),
					new SqlParameter("@HtmlTable_TableTrCssName", SqlDbType.VarChar,50),
					new SqlParameter("@DbOperat_OrderList", SqlDbType.VarChar,500),
					new SqlParameter("@DbOperat_SearchList", SqlDbType.VarChar,500),
					new SqlParameter("@DbSelect_UniteWhere", SqlDbType.VarChar,500),
                    new SqlParameter("@Operat_TagString", SqlDbType.VarChar,100)        ,
                     new SqlParameter("@Status_ControlId", SqlDbType.VarChar,50) 
                                        };
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = model.Status.ClassName;
            parameters[2].Value = model.DbSelect.TableName;
            parameters[3].Value = model.DbSelect.SelectFields;
            parameters[4].Value = model.DbSelect.OrderField;
            parameters[5].Value = model.DbSelect.PageIndex;
            parameters[6].Value = model.DbSelect.PageSize;
            parameters[7].Value = model.DbSelect.StrWhere;
            parameters[8].Value = model.DbSelect.KeyField;
            parameters[9].Value = model.HtmlTable.TableCssName;
            parameters[10].Value = model.HtmlTable.TBodyTrHtml;
            parameters[11].Value = model.Operat.OperatHtml;
            parameters[12].Value = model.Operat.IsHasOperat;
            parameters[13].Value = model.Operat.ColumnName;
            parameters[14].Value = model.DbOperat.DeleteSql;
            parameters[15].Value = model.Status.UserId;
            parameters[16].Value = model.HtmlFanye.FanyeHtml;
            parameters[17].Value = model.HtmlFanye.IsHasFanye;
            parameters[18].Value = model.DbSelect.OrderType;
            parameters[19].Value = model.HtmlTable.THeadTrHtml;
            parameters[20].Value = model.DbOperat.OperatSqlList;
            parameters[21].Value = model.HtmlTable.TableTrCssName;
            parameters[22].Value = model.DbOperat.OrderList;
            parameters[23].Value = model.DbOperat.SearchList;
            parameters[24].Value = model.DbSelect.UniteWhere;
            parameters[25].Value = model.Operat.TagString;
            parameters[26].Value = model.Status.ControlId;
            DbHelperSQL.RunProcedure(base.GetDataPaging_Model_ProcName(), parameters, out rowsAffected);
            return (int)parameters[0].Value;

			//StringBuilder strSql = new StringBuilder();
			//插入
			//strSql.Append("if not exists(select 1 from DataPaging where Status_UserId=@Status_UserId) begin  " +
						 // "insert into DataPaging(");

            //strSql.Append("Status_ClassName,DbSelect_TableName,DbSelect_SelectFields,DbSelect_OrderField,DbSelect_OrderType,DbSelect_PageIndex,DbSelect_PageSize,DbSelect_StrWhere,DbSelect_KeyField,HtmlTable_TableCssName,HtmlTable_TBodyTrHtml,HtmlTable_THeadTrHtml,Operat_OperatHtml,Operat_IsHasOperat,Operat_ColumnName,DbOperat_DeleteSql,Status_UserId,HtmlFanye_FanyeHtml,HtmlFanye_IsHasFanye,Where_KeyValue,DbOperat_OperatSqlList)");
            //strSql.Append(" values (");
            //strSql.Append("@ClassName,@DbSelect_TableName,@DbSelect_SelectFields,@DbSelect_OrderField,@DbSelect_OrderType,@DbSelect_PageIndex,@DbSelect_PageSize,@DbSelect_StrWhere,@DbSelect_KeyField,@HtmlTable_TableCssName,@HtmlTable_TBodyTrHtml,@HtmlTable_THeadTrHtml,@Operat_OperatHtml,@Operat_IsHasOperat,@Operat_ColumnName,@DbOperat_DeleteSql,@Status_UserId,@HtmlFanye_FanyeHtml,@HtmlFanye_IsHasFanye,@Where_KeyValue,@DbOperat_OperatSqlList)");
            //strSql.Append(";select @@IDENTITY ");
            ////跟新
            //strSql.Append("end else begin ");
		   
            //strSql.Append("update DataPaging set ");
            //strSql.Append("Status_ClassName=@ClassName,");
            //strSql.Append("DbSelect_TableName=@DbSelect_TableName,");
            //strSql.Append("DbSelect_SelectFields=@DbSelect_SelectFields,");
            //strSql.Append("DbSelect_OrderField=@DbSelect_OrderField,");
            //strSql.Append("DbSelect_OrderType=@DbSelect_OrderType,");
            //strSql.Append("DbSelect_PageIndex=@DbSelect_PageIndex,");
            //strSql.Append("DbSelect_PageSize=@DbSelect_PageSize,");
            //strSql.Append("DbSelect_StrWhere=@DbSelect_StrWhere,");
            //strSql.Append("DbSelect_KeyField=@DbSelect_KeyField,");
            //strSql.Append("HtmlTable_TableCssName=@HtmlTable_TableCssName,");
            //strSql.Append("HtmlTable_TBodyTrHtml=@HtmlTable_TBodyTrHtml,");
            //strSql.Append("HtmlTable_THeadTrHtml=@HtmlTable_THeadTrHtml,");
            //strSql.Append("Operat_OperatHtml=@Operat_OperatHtml,");
            //strSql.Append("Operat_IsHasOperat=@Operat_IsHasOperat,");
            //strSql.Append("Operat_ColumnName=@Operat_ColumnName,");
            //strSql.Append("DbOperat_DeleteSql=@DbOperat_DeleteSql,");
            //strSql.Append("Status_UserId=@Status_UserId,");
            //strSql.Append("HtmlFanye_FanyeHtml=@HtmlFanye_FanyeHtml,");
            //strSql.Append("HtmlFanye_IsHasFanye=@HtmlFanye_IsHasFanye,");
            ////strSql.Append("DbSelect_SearchWhere=@DbSelect_SearchWhere,");
            //strSql.Append("DbOperat_OperatSqlList=@DbOperat_OperatSqlList,");
            //strSql.Append("Where_KeyValue=@Where_KeyValue");
            //strSql.Append(" where Status_UserId=@Status_UserId ");

            //strSql.Append("end");
            //SqlParameter[] parameters =
            //{
            //    new SqlParameter("@ClassName", SqlDbType.NVarChar, 50),
            //    new SqlParameter("@DbSelect_TableName", SqlDbType.NVarChar, 50),
            //    new SqlParameter("@DbSelect_SelectFields", SqlDbType.NVarChar, 4000),
            //    new SqlParameter("@DbSelect_OrderField", SqlDbType.NVarChar, 4000),
            //    new SqlParameter("@DbSelect_OrderType", SqlDbType.Bit, 1),
            //    new SqlParameter("@DbSelect_PageIndex", SqlDbType.Int, 4),
            //    new SqlParameter("@DbSelect_PageSize", SqlDbType.Int, 4),
            //    new SqlParameter("@DbSelect_StrWhere", SqlDbType.NVarChar, 4000),
            //    new SqlParameter("@DbSelect_KeyField", SqlDbType.NVarChar, 50),
            //    new SqlParameter("@HtmlTable_TableCssName", SqlDbType.NVarChar, 50),
            //    new SqlParameter("@HtmlTable_TBodyTrHtml", SqlDbType.NVarChar, 4000),
            //    new SqlParameter("@HtmlTable_THeadTrHtml", SqlDbType.NVarChar, 50),
            //    new SqlParameter("@Operat_OperatHtml", SqlDbType.NVarChar, 4000),
            //    new SqlParameter("@Operat_IsHasOperat", SqlDbType.Bit, 1),
            //    new SqlParameter("@Operat_ColumnName", SqlDbType.NVarChar, 50),
            //    new SqlParameter("@DbOperat_DeleteSql", SqlDbType.NVarChar, 4000),
            //    new SqlParameter("@Id", SqlDbType.Int, 4),
            //    new SqlParameter("@Status_UserId", SqlDbType.Int, 4),
            //    new SqlParameter("@HtmlFanye_FanyeHtml", SqlDbType.NVarChar, 4000),
            //    new SqlParameter("@HtmlFanye_IsHasFanye", SqlDbType.Bit, 1),
            //    new SqlParameter("@Where_KeyValue", SqlDbType.NVarChar, 4000),
            //    new SqlParameter("@DbOperat_OperatSqlList", SqlDbType.NVarChar, 4000)
            //};
            //parameters[0].Value = model.Status.ClassName;
            //parameters[1].Value = model.DbSelect.TableName;
            //parameters[2].Value = model.DbSelect.SelectFields;
            //parameters[3].Value = model.DbSelect.OrderField;
            //parameters[4].Value = model.DbSelect.OrderType;
            //parameters[5].Value = model.DbSelect.PageIndex;
            //parameters[6].Value = model.DbSelect.PageSize;
            //parameters[7].Value = model.DbSelect.StrWhere;
            //parameters[8].Value = model.DbSelect.KeyField;
            //parameters[9].Value = model.HtmlTable.TableCssName;
            //parameters[10].Value = model.HtmlTable.TBodyTrHtml;
            //parameters[11].Value = model.HtmlTable.THeadTrHtml;
            //parameters[12].Value = model.Operat.OperatHtml;
            //parameters[13].Value = model.Operat.IsHasOperat;
            //parameters[14].Value = model.Operat.ColumnName;
            //parameters[15].Value = model.DbOperat.DeleteSql;
            //parameters[16].Value = model.Id;
            //parameters[17].Value = model.Status.UserId;
            //parameters[18].Value = model.AttrHtmlFanye.FanyeHtml;
            //parameters[19].Value = model.AttrHtmlFanye.IsHasFanye;
            //parameters[20].Value = model.Where_KeyValue;
			
            //parameters[21].Value = model.DbOperat.OperatSqlList;
            ////DbHelperSQL.ExecuteSql
            //if (model.Id == null || model.Id==0)
            //{
            //    object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            //    if (obj == null)
            //    {
            //        return 0;
            //    }
            //    else
            //    {
            //        return Convert.ToInt32(obj);
            //    }
            //}
            //else
            //{
            //    int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            //    if (rows > 0)
            //    {
            //        return 1;
            //    }
            //    else
            //    {
            //        return 0;
            //    }
            //}

		}

	   
	}
}
